#include "database.h"
#include "ui_database.h"

#include <QSqlError>
#include <QMessageBox>
#include <QDebug>

int count=0;

database::database(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::database)
{
    ui->setupUi(this);
    this->setFixedSize(700,650); // 设置窗口的固定大小
    this->setWindowTitle("——数据库管理平台——");
    this->setWindowIcon(QIcon(":/img/data_nie.ico"));
    Database_Init();
}

database::~database()
{
    delete ui;
}

void database::Database_Init()
{
     QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL"); //添加数据库的类型

     //添加本地数据库的基本的配置
     db.setHostName("127.0.0.1");
     db.setPort(3306);
     db.setDatabaseName("qtsql");
     db.setUserName("root");
     db.setPassword("qq-123");

     bool ok = db.open(); //查询数据是否打开
     if(ok){
//        QMessageBox::information(this,"info","database open success"); //查询数据库是否配置成功
        qDebug()<<"database open success";
     }
     else{
        QMessageBox::information(this, "警告！", "DataBase Open Failed，Check whether there is a database or whether the parameters are correct!!!");
        qDebug()<<"error open database because"<<db.lastError().text();
        ui->label->setText(db.lastError().text());
     }

     //数据库查新信息操作
    Query_AllData(); //初始化时候自动查询所有数据
    ui->tableView->horizontalHeader()->setVisible(true); //显示行号索引
    ui->tableView->verticalHeader()->setVisible(true); //显示行号索引
    //初始化界面的显示区
    ui->tableView->setColumnWidth(0,80); //序列号
    ui->tableView->setColumnWidth(1,150); //时间
    ui->tableView->setColumnWidth(2,70);
    ui->tableView->setColumnWidth(3,70);
    ui->tableView->setColumnWidth(4,70);
    ui->tableView->setColumnWidth(5,70);
    ui->tableView->setColumnWidth(6,70);
    ui->tableView->setColumnWidth(7,70);

    //初始化下拉框
    ui->comboBox->addItem("temp");
    ui->comboBox->addItem("humi");
    ui->comboBox->addItem("lignt");

    ui->comboBox->addItem("soil");
    ui->comboBox->addItem("mq2");
    ui->comboBox->addItem("rain");

    //设置日期查询
    ui->dateTimeEdit->setDateTime(QDateTime::currentDateTime().addSecs(0));
    ui->dateTimeEdit_2->setDateTime(QDateTime::currentDateTime().addSecs(0));
}

void database::Query_AllData()
{
    tableModel = new QSqlQueryModel;//定义一个数据库模型，指定父对象

    QString strSelectData = "select * from qtdata"; //从那个表查询自己的数据
    tableModel->setQuery(strSelectData);
    ui->tableView->setModel(tableModel);
}

void database::Query_Data()
{
    tableModel = new QSqlQueryModel;

    QString startTime = ui->dateTimeEdit->text();
    QString startTime2 = ui->dateTimeEdit_2->text();

    //查询操作
    QString strSelectData = "select * from qtdata where CurrentTime between " + startTime + "and" +
            startTime2 + ";";
}

bool database::clearDBTable()
{
    QSqlQuery query;
    QString strClearDB = "delete from qtdata";
    query.prepare(strClearDB);
    if(!query.exec()){
        qDebug()<<query.lastError();
    }
    return true;
}

void database::UpdataToDataBase(QString Stemp, QString Shumi, QString Slight, QString Ssoil, QString Smq2, QString Srain)
{

    float val[6];

    val[0] = Stemp.toFloat();
    val[1] = Shumi.toFloat();
    val[2] = Slight.toFloat();

    val[3] = Ssoil.toFloat();
    val[4] = Smq2.toFloat();
    val[5] = Srain.toFloat();


    qDebug()<<"插入数据库的数据为:"<<val[0]<<" "<<val[1]<<" "<<val[2]<<" "
                                <<val[3]<<" "<<val[4]<<" "<<val[5]<<" "<<" ";

    //插入数据到数据库中
    insertData(val);

    //查询并全部显示
    Query_AllData();
}

bool database::insertData(float val[6])
{
    QSqlQuery query;

    QString stInsertData = "insert into qtdata values (?,?,?,?,?,?,?,?);"; //插入数据库对应数据的字段
    query.prepare(stInsertData);

    query.addBindValue(count);

    query.addBindValue(QDateTime::currentDateTime().addSecs(0).toString("yyyy-MM-dd hh:mm:ss"));

    query.addBindValue(val[0]);
    query.addBindValue(val[1]);
    query.addBindValue(val[2]);

    query.addBindValue(val[3]);
    query.addBindValue(val[4]);
    query.addBindValue(val[5]);

    if(!query.exec())
    {
        qDebug()<<query.lastError();
    }
    count+=1;
    return true;
}


void database::on_select_data_bt_clicked()
{
    qDebug()<<"查询时间对应的数据";
    Query_Data();
}

void database::on_displayAll_bt_clicked()
{
    qDebug()<<"查询所有数据";
    Query_AllData();

}

void database::on_exit_bt_clicked()
{
    this->close(); // 关闭当前的窗口
}

void database::on_clear_bt_clicked()
{
    clearDBTable(); //
    count = 0;
    Query_AllData();
    ui->label->setText("数据清空完毕");
}

void database::on_currDay_bt_2_clicked()
{
    //上一天
    ui->dateTimeEdit->setDateTime(QDateTime::currentDateTime().addSecs(-3600*24));
    //当前时间
    ui->dateTimeEdit_2->setDateTime(QDateTime::currentDateTime().addSecs(0));

     Query_Data();

}

void database::on_currThDay_bt_2_clicked()
{
    //三天前天
   ui->dateTimeEdit->setDateTime(QDateTime::currentDateTime().addSecs(-3600*24*3));
   //当前时间
   ui->dateTimeEdit_2->setDateTime(QDateTime::currentDateTime().addSecs(0));

   Query_Data();
}

void database::on_currWeek_bt_2_clicked()
{
    //七天前
   ui->dateTimeEdit->setDateTime(QDateTime::currentDateTime().addSecs(-3600*24*7));
   //当前时间
   ui->dateTimeEdit_2->setDateTime(QDateTime::currentDateTime().addSecs(0));

   Query_Data();
}

void database::on_now_bt_clicked()
{
    //当前时间
    ui->dateTimeEdit_2->setDateTime(QDateTime::currentDateTime().addSecs(0));
}

//根据对应键值查询
void database::on_select_val_bt_clicked()
{
    //获取索引值
    QString query = ui->comboBox->currentText();
    qDebug()<<query;

    //获取上下值
    QString downNum = ui->spinBox->text();
    QString upNum = ui->spinBox_2->text();

    //查询操作指令
    QString strSelectData = "select * from qtdata where " + query +" >= "+ downNum + "&&" +
            query + "<="+upNum+";";

    tableModel->setQuery(strSelectData); //将查询语句执行起来
    ui->tableView->setModel(tableModel); //显示查询内容到显示区
}
